﻿CREATE PROCEDURE [maint].[UpdateStats]
@DbName [sysname], @CheckSchedule BIT=1, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @RC int, @ERROR int;
	SELECT @RC = 0, @ERROR = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID);
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + '''';
			PRINT N'	@CheckSchedule = N''' + ISNULL(CAST(@CheckSchedule AS nvarchar),N'NULL') + '''';
		END
		
	DECLARE @UpdateRequired bit;
	SET @UpdateRequired = 1;
	
	IF (@CheckSchedule = 1)
		EXEC @RC = [maint].[GetUpdateStatsScheduleStatus] @DbName, NULL, @UpdateRequired OUTPUT, @OutputDiagnostics;
	
	DECLARE @ActionToken CHAR(1);
	SET @ActionToken = 'S';

	IF(@UpdateRequired = 1)
		BEGIN
			--set error status flag, default to error to catch all
			EXEC maint.SetErrorStatus @DbName, @ActionToken, 1;
		END
	ELSE
		BEGIN
			--check previous status
			EXEC @RC = maint.CheckErrorStatus @DbName, @ActionToken;
			
			GOTO EXIT_PROC
		END
	
	PRINT REPLICATE('-',100);
	PRINT N'UPDATING STATISTICS FOR DATABASE ' + UPPER(@DbName);

	DECLARE @LatencyPeriod int, @CheckPointDate datetime;
	EXEC @RC = [maint].[GetStatsLatency] @DbName, @LatencyPeriod OUTPUT, @OutputDiagnostics;
	SET @CheckPointDate = DATEADD(hh, @LatencyPeriod * -1, GETDATE());

	--localise recordset to avoid 16958 cursor error
	DECLARE @StatsList TABLE([TableName] sysname, [StatsName] sysname, [StatsDate] datetime);
	DECLARE @StatsQuery nvarchar(1024)
	SET @StatsQuery = N'
			USE [' + @DbName + N'];
			SELECT
				N''['' + OBJECT_SCHEMA_NAME([object_id]) + N''].['' + OBJECT_NAME([object_id]) + N'']''
				,[name]
				,STATS_DATE([object_id],[stats_id])
			FROM sys.stats
			WHERE  (OBJECTPROPERTYEX([object_id],''IsMSShipped'') = 0) --ignore system table stats
				AND(DATEDIFF(hh,STATS_DATE([object_id],[stats_id]),@CheckPointDate) > 0)
				AND(STATS_DATE([object_id],[stats_id]) IS NOT NULL); --ignore stats for tables with no data
	';
	INSERT INTO @StatsList
	EXEC @RC = sp_executesql @StatsQuery, N'@CheckPointDate datetime', @CheckPointDate;
	IF(@RC <> 0) GOTO EXIT_PROC;

	DECLARE IDX_CUR CURSOR GLOBAL FAST_FORWARD FOR(SELECT * FROM @StatsList);
	OPEN IDX_CUR;
	DECLARE @TableName nvarchar(128), @StatsName nvarchar(128), @StatsDate datetime;
	FETCH NEXT FROM IDX_CUR INTO @TableName, @StatsName, @StatsDate;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		DECLARE @UpdateStatement nvarchar(512);
		SET @UpdateStatement = N'
			USE [' + @DbName + '];
			UPDATE STATISTICS ' + @TableName + N'([' + @StatsName + N']) WITH FULLSCAN';

		IF (@OutputDiagnostics = 1)
		BEGIN
			PRINT N'Statistics Name: ' + @TableName + N'(' + @StatsName + N')';
			PRINT N'Last Statistics Update: ' + CAST(@StatsDate AS nvarchar);
		END;

		BEGIN TRY
			EXEC @RC = sp_executesql @UpdateStatement;
		END TRY
		BEGIN CATCH
			SET @ERROR = @RC;
		END CATCH

		FETCH NEXT FROM IDX_CUR INTO @TableName, @StatsName, @StatsDate;
	END;
	CLOSE IDX_CUR;
	DEALLOCATE IDX_CUR;

	IF(@ERROR = 0)
		BEGIN
			--clear error status flag
			EXEC maint.SetErrorStatus @DbName, @ActionToken, 0;	
		END
	ELSE
		BEGIN
			SET @RC = @ERROR;			
			RAISERROR('Statistics update for database %s failed. Check previous error messages for details.',16,1,@DbName);
		END

EXIT_PROC:
	RETURN(@RC);
END